from util.common import render_template, random_string, write_file
from db.db_util import dataframe_from_db

if __name__ == '__main__':

    df = dataframe_from_db('select * from tf_smp_port_exe_result')

    df_extracted = df[['IP', 'PORT', 'FIND_TIME']]
    unique_ip_port = df_extracted.sort_values(by='FIND_TIME', ascending=False).drop_duplicates(subset=['IP', 'PORT'])

    w_sql_list = []
    for row in unique_ip_port.itertuples():
        seep = {
            "id": "INREZX" + random_string(14),
            "ip": row.IP,
            "name": None,
            "port": row.PORT,
            "startTime": row.FIND_TIME,
            "ipval": None,
        }
        message1 = render_template("""
                         INSERT INTO tf_sa_inteasset_result (id, asset_ip, asset_name, ASSET_PORT, PORT_TYPE, PORT_USAGE,
                                    source, tips, review_result, review_date, reviewer, sender,
                                    is_sended, send_date, change_id, close_date, dept, fac,
                                    facres, bossres, advice, fac_opin, reinforce_status,
                                    reinforce_desc, finder, report_status, report_date, find_date,
                                    reinforce_plandate, reinforcer, reinforce_date, opt_id, times,
                                    business_id, item_opin, asset_type, reinforce_scheme, asset_id,
                                    collect_echo, review_attach, review_proposal, remark, summary,
                                    retreat_advice, facdeal, IP_VAL)
                            VALUES ($id, $ip, $name, $port, '2', NULL, NULL, NULL, '-1', NULL, '',
                                    '0', '2', NULL, NULL, NULL, '中国移动福建省公司', NULL, NULL, '0', NULL, NULL, '-1',
                                    NULL, '0', NULL, NULL, $startTime, NULL, '0', NULL,
                                    NULL, 1, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
                                    $ipval);
                      """, seep)
        w_sql_list.append(message1)

    write_file('target/SATP_合规问题中心互联网资产.sql', list)
       
